<?php

namespace app\common\service;

use app\common\exception\AppLogicException;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExcelImporter
{
    private $spreadsheet;
    private $worksheet;
    private $dataRows;
    private $titles;
    private $fields;
    private array $titleCellColIndexes;

    private $rowIndex = 1;

    public function __construct($filePath,$rowIndex = 1)
    {
        // 读取 Excel 文件
        $this->spreadsheet = IOFactory::load($filePath);
        $this->worksheet = $this->spreadsheet->getActiveSheet();
        $this->rowIndex = $rowIndex;
    }

    /**
     * 设置标题行，并找到数据行
     *
     * @param array $headerMapping 标题名称与字段名及数据类型的映射
     * @param bool $noTitle
     * @throws Exception|AppLogicException
     */
    public function setHeaderMapping(array $headerMapping, bool $noTitle = false): void
    {
        $this->fields = $headerMapping['fields'];
        $this->titles = $headerMapping['titles'] ?? [];
        $this->checkExcelTitle($this->worksheet);

        $this->dataRows = $noTitle ? $this->getDataRows() : $this->getDataRowsByTitle();;
    }

    /**
     * 检查表格标题
     * @param Worksheet $worksheet
     * @return void
     * @throws AppLogicException|Exception
     */
    private function checkExcelTitle(Worksheet $worksheet): void
    {
        foreach ($worksheet->getRowIterator() as $row) {
            foreach ($row->getCellIterator() as $key => $cell) {

                $columnIndex = Coordinate::columnIndexFromString($key);

                foreach ($this->titles as $title) {

                    if($cell->getValue() instanceof RichText){
                        $value = $cell->getValue()->__toString();
                    }else{
                        $value = $cell->getValue();
                    }

                    if ($value === $title) {
                        $this->titleCellColIndexes[trim($title)] = $key;
                    }
                }
            }
            break;
        }
    }

    /**
     * 获取标题cell对应列索引
     * @param string $title
     * @return mixed|null
     */
    private function getTitleCellColIndex(string $title): mixed
    {
        return $this->titleCellColIndexes[$title] ?? null;
    }

    /**
     * 获取数据行
     *
     * @return array
     * @throws Exception
     */
    private function getDataRowsByTitle(): array
    {
        $rows = [];

        foreach ($this->worksheet->getRowIterator() as $row) {

            if ($row->getRowIndex() <= $this->worksheet->getHighestRow() && $row->getRowIndex() > $this->rowIndex) {
                $rowData = [];

                foreach ($this->fields as $title => $config) {
                    if (is_array($config)) {
                        $colIndex = $this->getTitleCellColIndex($title);

                        if ($colIndex) {
//                            var_dump($colIndex . $row->getRowIndex());
                            $cellValue = $this->worksheet->getCell($colIndex . $row->getRowIndex())->getValue();

//                            var_dump($cellValue);
                            $castValue = $this->castValue($cellValue, $rowData, $config);

                            $config[0] && $rowData[$config[0]] = $castValue;
                        } elseif (!empty($config[3])) {
                            $colIndex = $this->getTitleCellColIndex($config[3]);
                            $cellValue = $this->worksheet->getCell($colIndex . $row->getRowIndex())->getValue();


                            $rowData[$config[0]] = $this->castValue($cellValue, $rowData, $config);
                        } else {
                            $rowData[$config[0]] = '';
                        }
                    } else {
                        $colIndex = $this->getTitleCellColIndex($title);

                        if ($colIndex){
                            $cellValue = $this->worksheet->getCell($colIndex . $row->getRowIndex())->getValue();

                            $castValue = $this->castValue($cellValue, $rowData, [$config => $cellValue]);
                            $rowData[$config] = $castValue;
                        }else{
                            $rowData[$config] = '';

                        }

                    }
                }
                $rows[] = $rowData;
            }
        }



        return $rows;
    }

    /**
     * 获取数据行
     *
     * @return array
     * @throws Exception
     */
    private function getDataRows(): array
    {
        $rows = [];
        $defRowData = [];
        foreach ($this->fields as $key => $config) {
            $k = is_numeric($key) ? $key : $config[0];
            $defRowData[$k] = '';
        }
        foreach ($this->worksheet->getRowIterator() as $row) {
            $rowData = $defRowData;
            foreach ($row->getCellIterator() as $cell) {
                $colIndex = $cell->getColumn();
                $cellValue = $cell->getValue();
                $config = $this->fields[$colIndex] ?? null;
                if ($config) {
                    $castValue = $this->castValue($cellValue, $rowData, $config);
                    $config[0] && $rowData[$config[0]] = $castValue;
                }
            }
            $extra = $rowData['_extra'] ?? [];
            unset($rowData['_extra']);
            $extra && $rowData = array_merge($rowData, $extra);
            $rows[] = $rowData;
        }
        return $rows;
    }

    /**
     * 根据值查找单元格
     *
     * @param Worksheet $worksheet
     * @param string $value
     * @return Cell|null
     */
    private function findCellByValue(Worksheet $worksheet, string $value): ?Cell
    {
        foreach ($worksheet->getRowIterator() as $row) {
            foreach ($row->getCellIterator() as $cell) {
                if ($cell->getValue() === $value) {
                    return $cell;
                }
            }
        }
        return null;
    }

    /**
     * 转换单元格值的数据类型
     *
     * @param mixed $value
     * @param mixed $rowData
     * @param array $config
     * @return mixed
     */
    private function castValue(mixed $value, mixed &$rowData, array $config): mixed
    {
        $type = $config[1] ?? null;


        $format = $config[2] ?? null;

        if ($type instanceof \Closure) {
            return $type($value, $rowData);
        }

        switch ($type) {
            case 'int':
                return (int)$value;
            case 'float':
                return (float)$value;
            case 'price':
                return sprintf('%.2f', $value);
            case 'datetime':
                if ($format === 'timestamp') {
                    return Date::excelToTimestamp($value);
                } elseif ($format) {
                    return Date::excelToDateTimeObject($value)->format($format);
                } else {
                    return Date::excelToDateTimeObject($value)->format('Y-m-d H:i:s');
                }
            case 'text':
            default:
                return (string)$value;
        }
    }

    /**
     * 解析 Excel 数据
     *
     * @return array
     */
    public function parse(): array
    {
        return $this->dataRows;
    }
}